/**
 * @Title: ProductManageDao.java
 * 
 * @Description:
 * 
 * @Copyright: Copyright (c) 2010
 * 
 * @Company: www.infoservice.com.cn
 * @Date: 2010-7-1
 * 
 * @author yuyong
 * @mail yuyong@infoservice.com.cn
 * @version 1.0
 * @remark
 */
package com.infodms.dms.dao.productmanage;

import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.infodms.dms.common.Constant;
import com.infodms.dms.dao.common.BaseDao;
import com.infodms.dms.po.TmVhclMaterialGroupPO;
import com.infodms.dms.po.TmVhclMaterialGroupRPO;
import com.infodms.dms.po.TmVhclMaterialPO;
import com.infodms.dms.po.TmpVsPriceDtlPO;
import com.infodms.dms.util.CommonUtils;
import com.infoservice.po3.bean.PO;
import com.infoservice.po3.bean.PageResult;

/**
 * @author yuyong
 * 
 */
public class ProductManageDao extends BaseDao<PO> {
	public static Logger logger = Logger.getLogger(ProductManageDao.class);
	private static final ProductManageDao dao = new ProductManageDao();

	public static final ProductManageDao getInstance() {
		return dao;
	}

	@Override
	protected PO wrapperPO(ResultSet rs, int idx) {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * 物料组维护查询列表
	 * 
	 * @param map
	 * @param curPage
	 * @param pageSize
	 * @return
	 */
	public PageResult<Map<String, Object>> getMaterialGroupManageQueryList(
			Map<String, Object> map, int curPage, int pageSize) {

		String groupCode = (String) map.get("groupCode");
		String groupName = (String) map.get("groupName");
		String parentGroupCode = (String) map.get("parentGroupCode");
		String status = (String) map.get("status");
		String companyId = (String) map.get("companyId");

		StringBuffer sql = new StringBuffer();

		sql
				.append("SELECT TVMG1.GROUP_ID, TVMG1.GROUP_CODE, TVMG1.GROUP_NAME, TVMG1.STATUS, TVMG1.GROUP_LEVEL\n");
		sql
				.append("  FROM TM_VHCL_MATERIAL_GROUP TVMG1, TM_VHCL_MATERIAL_GROUP TVMG2\n");
		sql.append(" WHERE TVMG1.PARENT_GROUP_ID = TVMG2.GROUP_ID(+)\n");
		if (!groupCode.equals("")) {
			sql.append("   AND TVMG1.GROUP_CODE LIKE '%" + groupCode + "%'\n");
		}
		if (!groupName.equals("")) {
			sql.append("   AND TVMG1.GROUP_NAME LIKE '%" + groupName + "%'\n");
		}
		if (!status.equals("")) {
			sql.append("   AND TVMG1.STATUS =" + status + "\n");
		}
		if (!companyId.equals("")) {
			sql.append("   AND TVMG1.COMPANY_ID =" + companyId + "\n");
		}
		if (!parentGroupCode.equals("")) {
			sql.append("   AND TVMG2.GROUP_CODE LIKE '%" + parentGroupCode
					+ "%'");
		}
		sql.append("ORDER BY TVMG1.GROUP_LEVEL,TVMG1.GROUP_CODE") ;
		PageResult<Map<String, Object>> ps = pageQuery(
				sql.toString(),
				null,
				"com.infodms.dms.dao.productmanage.ProductManageDao.getMaterialGroupManageQueryList",
				pageSize, curPage);
		return ps;
	}

	public TmVhclMaterialGroupPO getTmVhclMaterialGroupPO(
			TmVhclMaterialGroupPO po) {
		List<PO> list = dao.select(po);
		TmVhclMaterialGroupPO result = list.size() != 0 ? (TmVhclMaterialGroupPO) list
				.get(0)
				: null;
		return result;
	}

	/**
	 * 物料维护查询列表
	 * 
	 * @param map
	 * @param curPage
	 * @param pageSize
	 * @return
	 */
	public PageResult<Map<String, Object>> getMaterialManageQueryList(
			Map<String, Object> map, int curPage, int pageSize) {

		String materialCode = (String) map.get("materialCode");
		String materialName = (String) map.get("materialName");
		String status = (String) map.get("status");
		String groupCode = (String) map.get("groupCode");
		String companyId = (String) map.get("companyId");

		StringBuffer sql = new StringBuffer("\n");

		sql.append("SELECT TVM.MATERIAL_ID,\n");
		sql.append("       TVM.MATERIAL_CODE,\n");  
		sql.append("       TVM.MATERIAL_NAME,\n");  
		sql.append("       TVM.ERP_NAME, --ERP名称\n");  
		sql.append("       TVM.COLOR_CODE,\n");  
		sql.append("       TVM.COLOR_NAME,\n");  
		sql.append("       DECODE(TVM.ORDER_FLAG, 10331001, '可提报', '不可提报') ORDER_FLAG, ---是否可提报\n");  
		sql.append("       TVM.STATUS\n");  
		sql.append("  FROM TM_VHCL_MATERIAL TVM\n");  
		sql.append(" WHERE 1 = 1\n");  
		
		if (!CommonUtils.isNullString(materialCode)) {
			sql.append("   AND TVM.MATERIAL_CODE LIKE '%").append(materialCode).append("%'\n");  
		}
		if (!CommonUtils.isNullString(materialName)) {
			sql.append("   AND TVM.MATERIAL_NAME LIKE '%").append(materialName).append("%'\n");  
		}
		if (!CommonUtils.isNullString(companyId)) {
			sql.append("   AND TVM.COMPANY_ID = ").append(companyId).append("\n");  
		}
		if (!CommonUtils.isNullString(status)) {
			sql.append("   AND TVM.STATUS = ").append(status).append("\n");  
		}
		if (!CommonUtils.isNullString(groupCode)) {
			sql.append("   AND TVM.MATERIAL_ID IN\n");  
			sql.append("       (SELECT MGR.MATERIAL_ID\n");  
			sql.append("          FROM TM_VHCL_MATERIAL_GROUP_R MGR, TM_VHCL_MATERIAL_GROUP VMG\n");  
			sql.append("         WHERE MGR.GROUP_ID = VMG.GROUP_ID\n");  
			sql.append("           AND VMG.GROUP_CODE LIKE '%").append(groupCode).append("%')\n");
		}


		PageResult<Map<String, Object>> ps = pageQuery(
				sql.toString(),
				null,
				"com.infodms.dms.dao.productmanage.ProductManageDao.getMaterialManageQueryList",
				pageSize, curPage);
		return ps;
	}

	/**
	 * 获得整车物料po
	 * 
	 * @param po
	 * @return
	 */
	public TmVhclMaterialPO getTmVhclMaterialPO(TmVhclMaterialPO po) {
		List<PO> list = dao.select(po);
		TmVhclMaterialPO result = list.size() != 0 ? (TmVhclMaterialPO) list
				.get(0) : null;
		return result;
	}

	/**
	 * 获得整车物料与物料组关系po
	 * 
	 * @param po
	 * @return
	 */
	public TmVhclMaterialGroupRPO getTmVhclMaterialGroupRPO(
			TmVhclMaterialGroupRPO po) {
		List<PO> list = dao.select(po);
		TmVhclMaterialGroupRPO result = list.size() != 0 ? (TmVhclMaterialGroupRPO) list
				.get(0)
				: null;
		return result;
	}
	/*
	 * 获得特殊价格列表
	 */
	public List<Map<String, Object>> getmyPriceTypeList(Long companyId,String dealerId) {

		StringBuffer sql = new StringBuffer();

//		sql.append("SELECT *\n");
//		sql.append("FROM (\n");  
//		sql.append("SELECT TVP.PRICE_ID,\n");  
//		sql.append("       TVP.PRICE_CODE,\n");  
//		sql.append("       TVP.PRICE_DESC,\n");  
//		sql.append("       TC.CODE_DESC,\n");  
//		sql.append("       TDPR.RELATION_ID\n");  
//		sql.append("  FROM TT_VS_PRICE              TVP,\n");  
//		sql.append("       TM_DEALER_PRICE_RELATION TDPR,\n");  
//		sql.append("       TC_CODE                  TC,\n");  
//		sql.append("       TM_DEALER                TD\n");  
//		sql.append(" WHERE TRUNC(TVP.START_DATE) <= TRUNC(SYSDATE)\n");  
//		sql.append("   AND TRUNC(TVP.END_DATE) >= TRUNC(SYSDATE)\n");  
//		sql.append("   AND TDPR.PRICE_ID = TVP.PRICE_ID\n");  
//		sql.append("   AND TDPR.DEALER_ID = TD.DEALER_ID\n");  
//		sql.append("   AND TDPR.IS_DEFAULT = TC.CODE_ID\n");  
//		sql.append("   AND TVP.COMPANY_ID = " + companyId + "\n");  
//		sql.append("   AND TDPR.DEALER_ID = '" + dealerId + "'\n");  
//		sql.append("   UNION\n");  
//		sql.append("SELECT TVPA.PRICE_ID,\n");  
//		sql.append("       TVPA.PRICE_CODE,\n");  
//		sql.append("       TVPA.PRICE_DESC,\n");  
//		sql.append("       TCA.CODE_DESC,\n");  
//		sql.append("       TDPRA.RELATION_ID\n");  
//		sql.append("  FROM TT_VS_PRICE              TVPA,\n");  
//		sql.append("       TM_DEALER_PRICE_RELATION TDPRA,\n");  
//		sql.append("       TC_CODE TCA,\n");  
//		sql.append("       TM_DEALER                TDA\n");  
//		sql.append(" WHERE TDPRA.PRICE_ID = TVPA.PRICE_ID\n");  
//		sql.append("   AND TDPRA.DEALER_ID = TDA.DEALER_ID\n");  
//		sql.append("   AND TDPRA.IS_DEFAULT = TCA.CODE_ID\n");  
//		sql.append("   AND TVPA.CREATE_BY = '-100'\n");  
//		sql.append("   AND TVPA.COMPANY_ID = " + companyId + "\n");  
//		sql.append("   AND TDPRA.DEALER_ID = '" + dealerId + "') B\n");  
//		sql.append(" ORDER BY B.CODE_DESC DESC, B.PRICE_DESC\n");
//		sql.append("SELECT *\n");
//	    sql.append("FROM (\n");  
//	    sql.append("SELECT rownum,TVP.PRICE_ID,\n");  
//	    sql.append("       TVP.PRICE_CODE,\n");  
//	    sql.append("       TVP.PRICE_DESC,\n");  
//	    sql.append("       TC.CODE_DESC,\n");  
//	    sql.append("       TDPR.RELATION_ID,TVP.CREATE_DATE\n");  
//	    sql.append("  FROM TT_VS_PRICE              TVP,\n");  
//	    sql.append("       TM_DEALER_PRICE_RELATION TDPR,\n");  
//	    sql.append("       TC_CODE                  TC,\n");  
//	    sql.append("       TM_DEALER                TD\n");  
//	    sql.append(" WHERE TRUNC(TVP.START_DATE) <= TRUNC(SYSDATE)\n");  
//	    sql.append("   AND TRUNC(TVP.END_DATE) >= TRUNC(SYSDATE)\n");  
//	    sql.append("   AND TDPR.PRICE_ID = TVP.PRICE_ID\n");  
//	    sql.append("   AND TDPR.DEALER_ID = TD.DEALER_ID\n");  
//	    sql.append("   AND TDPR.IS_DEFAULT = TC.CODE_ID\n");  
//	    sql.append("	AND TDPR.CREATE_BY = '-1'\n");
//	    sql.append("   AND TVP.COMPANY_ID = " + companyId + "\n");  
//	    sql.append("   AND TDPR.DEALER_ID = '" + dealerId + "'\n");  
//	    sql.append("   AND ROWNUM=1  order by TVP.CREATE_DATE DESC  ");
//	    sql.append("   UNION\n");  
	    sql.append("SELECT TVPA.PRICE_ID,\n");  
	    sql.append("       TVPA.PRICE_CODE,\n");  
	    sql.append("       TVPA.PRICE_DESC,\n");  
	    sql.append("       TCA.CODE_DESC,\n");
	    sql.append("       TVPA.START_DATE,\n"); 
	    sql.append("       TVPA.END_DATE,\n"); 
	    sql.append("       TVPA.CREATE_DATE,\n"); 
	    sql.append("       TDPRA.RELATION_ID\n");  
	    sql.append("  FROM vw_TT_VS_PRICE              TVPA,\n");  
	    sql.append("       vw_TM_DEALER_PRICE_RELATION TDPRA,\n");  
	    sql.append("       TC_CODE TCA,\n");  
	    sql.append("       TM_DEALER                TDA\n");  
	    sql.append(" WHERE TDPRA.PRICE_ID = TVPA.PRICE_ID\n");  
	    sql.append("   AND TDPRA.DEALER_ID = TDA.DEALER_ID\n");  
	    sql.append("   AND TDPRA.IS_DEFAULT = TCA.CODE_ID\n");  
	    sql.append("	 AND TDPRA.CREATE_BY!=-1");
	    sql.append("	 AND TDPRA.CREATE_BY!=-100");
	    //sql.append("   AND TDPRA.IS_DEFAULT="+Constant.IF_TYPE_YES+"\n");
	    //sql.append("   AND TVPA.CREATE_BY = '-100'\n");  
	    sql.append("   AND TVPA.COMPANY_ID = " + companyId + "\n");  
	    sql.append("   AND TDPRA.DEALER_ID = '" + dealerId + "'\n");  
	    sql.append(" ORDER BY TCA.CODE_DESC DESC, TVPA.PRICE_DESC\n");

		List<Map<String, Object>> list = pageQuery(sql.toString(), null,
				"com.infodms.dms.dao.productmanage.ProductManageDao.getmyPriceTypeList");
		return list;
	}
	/*
	 * 获得特殊价格排除列表
	 */
	public PageResult<Map<String, Object>> getmyOtherPriceTypeList(String priceDesc,Long companyId,String dealerId,int curPage, int pageSize) {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.PRICE_ID, A.PRICE_DESC, A.CREATE_DATE\n");
		sql.append("  FROM vw_TT_VS_PRICE A\n");  
		//sql.append(" WHERE TRUNC(A.START_DATE) <= TRUNC(SYSDATE)\n");  
		//sql.append("   AND TRUNC(NVL(A.END_DATE, SYSDATE + 1)) >= TRUNC(SYSDATE)\n");  
		sql.append("   WHERE A.COMPANY_ID = " + companyId + "\n");  
		if (null != priceDesc && !"".equals(priceDesc)) {
			sql.append("AND A.PRICE_DESC LIKE '%"+priceDesc.trim()+"%'\n");
		}
		// 新添加个列表，该列表中可能无开始与结束时间，由B.CREATE_BY=-100标示
		sql.append("UNION\n");
		sql.append("SELECT B.PRICE_ID, B.PRICE_DESC, B.CREATE_DATE\n");  
		sql.append("  FROM vw_TT_VS_PRICE B\n");  
		sql.append(" WHERE B.COMPANY_ID = " + companyId + "\n");  
		sql.append("   AND B.CREATE_BY = '-100'\n");
		if (null != priceDesc && !"".equals(priceDesc)) {
			sql.append("AND B.PRICE_DESC LIKE '%"+priceDesc.trim()+"%'\n");
		}
//		sql.append("MINUS\n");  
//		sql.append("SELECT TVP.PRICE_ID, TVP.PRICE_DESC, TVP.CREATE_DATE\n");  
//		sql.append("  FROM TT_VS_PRICE              TVP,\n");  
//		sql.append("       TM_DEALER_PRICE_RELATION TDPR,\n");  
//		sql.append("       TM_DEALER                TD\n");  
//		sql.append("   WHERE TVP.COMPANY_ID = " + companyId + "\n");  
//		sql.append("   AND TDPR.PRICE_ID = TVP.PRICE_ID\n");  
//		sql.append("   AND TDPR.DEALER_ID = '" + dealerId + "'\n");

		PageResult<Map<String, Object>> ps = pageQuery(sql.toString(), null,
				"com.infodms.dms.dao.productmanage.ProductManageDao.getmyOtherPriceTypeList", pageSize, curPage);
		return ps;
	}
	/**
	 * 获得价格类型列表
	 * 
	 * @return
	 */
	public List<Map<String, Object>> getPriceTypeList(Long companyId) {

		StringBuffer sql = new StringBuffer();

		sql.append("SELECT TVP.PRICE_ID, TVP.PRICE_DESC\n");
		sql.append("  FROM vw_TT_VS_PRICE TVP\n");
		sql.append(" WHERE TVP.START_DATE < SYSDATE\n");
		sql.append("   AND TVP.END_DATE > SYSDATE");
		sql.append("   AND TVP.COMPANY_ID = " + companyId + "");

		List<Map<String, Object>> list = pageQuery(sql.toString(), null,
				"com.infodms.dms.dao.productmanage.ProductManageDao.getPriceTypeList");
		return list;
	}

	/**
	 * 物料维护查询列表
	 * 
	 * @param map
	 * @param curPage
	 * @param pageSize
	 * @return
	 */
	public PageResult<Map<String, Object>> getMaterialPriceManageQueryList(
			Map<String, Object> map, int curPage, int pageSize,String priceDesc) {

		String groupCode = (String) map.get("groupCode");
		String priceId = (String) map.get("priceId");
		String companyId = (String) map.get("companyId");

		StringBuffer sql = new StringBuffer();

		sql.append("SELECT TVM.GROUP_CODE,\n");
		sql.append("       TVM.GROUP_NAME,\n");
		sql.append("       TVP.PRICE_CODE,\n");
		sql.append("       TVP.PRICE_DESC,\n");
		sql.append("       TVPD.SALES_PRICE,\n");
		sql.append("       TVPD.NO_TAX_PRICE\n");
		sql.append("  FROM vw_TT_VS_PRICE_DTL TVPD, vw_TT_VS_PRICE TVP, TM_VHCL_MATERIAL_GROUP TVM\n");
		sql.append(" WHERE TVPD.PRICE_ID = TVP.PRICE_ID\n");
		sql.append("   AND TVPD.GROUP_ID = TVM.GROUP_ID\n");
		//sql.append("   AND TVP.START_DATE < SYSDATE\n");
		//sql.append("   AND TVP.END_DATE > SYSDATE\n");
		if(!priceDesc.equals("")){
			sql.append("AND TVP.price_desc like '%"+priceDesc+"%'\n");
		}
		if (!groupCode.equals("")) {
			sql.append("   AND TVM.GROUP_CODE LIKE '%" + groupCode + "%'\n");
		}
		if (!priceId.equals("")) {
			sql.append("   AND TVP.PRICE_ID = " + priceId + "");
		}
		if (!companyId.equals("")) {
			sql.append("   AND TVP.COMPANY_ID = " + companyId + "");
		}

		PageResult<Map<String, Object>> ps = pageQuery(sql.toString(),null, getFunName(), pageSize, curPage);
		return ps;
	}

	public PageResult<Map<String, Object>> getMaterialPriceManageTypeQueryList(
			Map<String, Object> map, int curPage, int pageSize) {

		String companyId = (String) map.get("companyId");

		StringBuffer sql = new StringBuffer();

		sql.append("SELECT TVP.PRICE_ID,\n");
		sql.append("       TVP.PRICE_CODE,\n");
		sql.append("       TVP.PRICE_DESC,\n");
		sql
				.append("       TO_CHAR(TVP.START_DATE, 'YYYY-MM-DD') START_DATE,\n");
		sql.append("       TO_CHAR(TVP.END_DATE, 'YYYY-MM-DD') END_DATE\n");
		sql.append("  FROM vw_TT_VS_PRICE TVP");
		sql.append(" WHERE 1=1\n");
		if (!companyId.equals("")) {
			sql.append("   AND TVP.COMPANY_ID = " + companyId + "");
		}

		PageResult<Map<String, Object>> ps = pageQuery(
				sql.toString(),
				null,
				"com.infodms.dms.dao.productmanage.ProductManageDao.getMaterialPriceManageTypeQueryList",
				pageSize, curPage);
		return ps;
	}

	/**
	 * 查询每条数据在临时表是否重复 return: boolean true不重复，false重复
	 * 
	 * @param paraPo
	 * @return
	 */
	public boolean isDateDump(TmpVsPriceDtlPO paraPo) {
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql = new StringBuffer("");
		boolean bl = true;

		sql.append("SELECT COUNT(*) AMT \n");
		sql.append("  from TMP_VS_PRICE_DTL P\n");
		sql.append(" WHERE P.PRICE_CODE = ?\n");
		params.add(paraPo.getPriceCode());
		sql.append("   AND P.GROUP_CODE = ?\n");
		params.add(paraPo.getGroupCode());
		sql.append("   AND P.USER_ID = ?\n");
		params.add(paraPo.getUserId());

		List<Map<String, Object>> list = dao.pageQuery(sql.toString(), params,
				getFunName());
		if (null != list && list.size() > 0) {
			Map<String, Object> map = list.get(0);
			Integer amt = new Integer(map.get("AMT").toString());
			if (amt.intValue() > 1) {
				bl = false;
			}
		}
		return bl;
	}

	public PageResult<Map<String, Object>> getMaterialPriceManageImportTempList(
			Map<String, Object> map, int curPage, int pageSize) {

		String userId = (String) map.get("userId");

		StringBuffer sql = new StringBuffer();

		sql.append("SELECT TVPD.PRICE_CODE,\n");
		sql.append("       TVPD.GROUP_CODE,\n");
		sql.append("       TVPD.SALES_PRICE,\n");
		sql.append("       TVPD.NO_TAX_PRICE\n");
		sql.append("  FROM TMP_VS_PRICE_DTL TVPD\n");
		sql.append(" WHERE TVPD.USER_ID = " + userId + "");

		PageResult<Map<String, Object>> ps = pageQuery(sql.toString(),null,getFunName(),pageSize, curPage);
		return ps;
	}

	public void insertTtVsPriceDtl(Map<String, Object> map) {

		String userId = (String) map.get("userId");

		StringBuffer sql = new StringBuffer();

		sql.append("MERGE INTO TT_VS_PRICE_DTL TVPD\n");
		sql.append("USING (SELECT TVP.PRICE_ID,\n");
		sql.append("              TVM.GROUP_ID,\n");
		sql.append("              TVPD.SALES_PRICE,\n");
		sql.append("              TVPD.NO_TAX_PRICE\n");
		sql.append("         FROM TMP_VS_PRICE_DTL TVPD, TT_VS_PRICE TVP, TM_VHCL_MATERIAL_GROUP TVM\n");
		sql.append("        WHERE TVPD.PRICE_CODE = TVP.PRICE_CODE\n");
		sql.append("          AND TVPD.GROUP_CODE = TVM.GROUP_CODE\n");
		sql.append("          AND TVPD.USER_ID = " + userId + ") TEMP\n");
		sql.append("ON (TVPD.PRICE_ID = TEMP.PRICE_ID AND TVPD.GROUP_ID = TEMP.GROUP_ID)\n");
		sql.append("WHEN MATCHED THEN\n");
		sql.append("  UPDATE\n");
		sql.append("     SET TVPD.SALES_PRICE  = TEMP.SALES_PRICE,\n");
		sql.append("         TVPD.NO_TAX_PRICE = TEMP.NO_TAX_PRICE\n");
		sql.append("WHEN NOT MATCHED THEN\n");
		sql.append("  INSERT\n");
		sql.append("    (DETAIL_ID,\n");
		sql.append("     PRICE_ID,\n");
		sql.append("     GROUP_ID,\n");
		sql.append("     SALES_PRICE,\n");
		sql.append("     NO_TAX_PRICE,\n");
		sql.append("     CREATE_BY,\n");
		sql.append("     CREATE_DATE)\n");
		sql.append("  VALUES\n");
		sql.append("    (F_GETID,\n");
		sql.append("     TEMP.PRICE_ID,\n");
		sql.append("     TEMP.GROUP_ID,\n");
		sql.append("     TEMP.SALES_PRICE,\n");
		sql.append("     TEMP.NO_TAX_PRICE,\n");
		sql.append("     " + userId + ",\n");
		sql.append("     SYSDATE)");

		dao.update(sql.toString(), null);
	}

	/**
	 * 获得索赔车型组列表
	 * 
	 * @param wrgroupType
	 * @return
	 */
	public List<Map<String, Object>> getModelGroupList(Integer wrgroupType,
			Long companyId) {

		StringBuffer sql = new StringBuffer();

		sql.append("SELECT TAWMG.WRGROUP_ID, TAWMG.WRGROUP_NAME\n");
		sql.append("  FROM TT_AS_WR_MODEL_GROUP TAWMG\n");
		sql.append(" WHERE TAWMG.WRGROUP_TYPE = " + wrgroupType + "\n");
		sql.append(" AND TAWMG.OEM_COMPANY_ID = " + companyId + "");

		List<Map<String, Object>> list = pageQuery(sql.toString(), null,
				"com.infodms.dms.dao.productmanage.ProductManageDao.getModelGroupList");
		return list;
	}
	public Map<String, Object> getMyMap(Long companyId,String dealerId) {

		StringBuffer sql = new StringBuffer();
	    sql.append("SELECT TVP.PRICE_ID,\n");  
	    sql.append("       TVP.PRICE_CODE,\n");  
	    sql.append("       TVP.PRICE_DESC,\n"); 
	    sql.append("       TVP.START_DATE,\n"); 
	    sql.append("       TVP.END_DATE,\n"); 
	    sql.append("       TVP.CREATE_DATE,\n");
	    sql.append("       TC.CODE_DESC,\n");  
	    sql.append("       TDPR.RELATION_ID,TVP.CREATE_DATE\n");  
	    sql.append("  FROM vw_TT_VS_PRICE              TVP,\n");  
	    sql.append("       vw_TM_DEALER_PRICE_RELATION TDPR,\n");  
	    sql.append("       TC_CODE                  TC,\n");  
	    sql.append("       TM_DEALER                TD\n");  
	    sql.append(" WHERE TDPR.PRICE_ID = TVP.PRICE_ID\n");  
	    sql.append("   AND TDPR.DEALER_ID = TD.DEALER_ID\n");  
	    sql.append("   AND TDPR.IS_DEFAULT = TC.CODE_ID\n");  
	    sql.append("	AND TDPR.CREATE_BY = '-1'\n");
	    sql.append("   AND TVP.COMPANY_ID = " + companyId + "\n");  
	    sql.append("   AND TDPR.DEALER_ID = '" + dealerId + "'\n");  
	    sql.append("   order by TVP.CREATE_DATE DESC  ");
	    //sql.append(" ORDER BY TC.CODE_DESC DESC, TVP.PRICE_DESC\n");
		List<Map<String, Object>> list = pageQuery(sql.toString(), null,
				"com.infodms.dms.dao.productmanage.ProductManageDao.getMyMap");
		return list.size() != 0 ? (Map<String, Object>) list.get(0) : null;
	}

	/**
	 * 获得索赔车型组
	 * 
	 * @param modelId
	 * @param wrgroupType
	 * @return
	 */
	public Map<String, Object> getModelGroup(Long modelId, Integer wrgroupType) {

		StringBuffer sql = new StringBuffer();

		sql
				.append("SELECT TAWMI.MODEL_ID, TAWMI.WRGROUP_ID, TAWMG.WRGROUP_TYPE\n");
		sql
				.append("  FROM TT_AS_WR_MODEL_ITEM TAWMI, TT_AS_WR_MODEL_GROUP TAWMG\n");
		sql.append(" WHERE TAWMI.WRGROUP_ID = TAWMG.WRGROUP_ID\n");
		sql.append("   AND TAWMI.MODEL_ID = " + modelId + "\n");
		sql.append("   AND TAWMG.WRGROUP_TYPE = " + wrgroupType);

		List<Map<String, Object>> list = pageQuery(sql.toString(), null,
				"com.infodms.dms.dao.productmanage.ProductManageDao.getModelGroup");
		return list.size() != 0 ? (Map<String, Object>) list.get(0) : null;
	}

	public String getMaterialGroupTreeCode(String parentId) {

		StringBuffer sql = new StringBuffer();

		sql.append("SELECT F_GET_MATERIALGROUPCODE('" + parentId
				+ "') CODE FROM DUAL\n");

		List<Map<String, Object>> list = pageQuery(sql.toString(), null,
				"com.infodms.dms.dao.productmanage.ProductManageDao.getMaterialGroupTreeCode");

		Map<String, Object> map = list.size() != 0 ? (Map<String, Object>) list
				.get(0) : null;
		String code = (map == null ? "" : (String) map.get("CODE"));
		return code;
	}

	/**
	 * 配额导入校验物料是否存在
	 * 
	 * @param map
	 * @return
	 */
	public List<Map<String, Object>> quotaImportCheckMaterial(Map<String, Object> map) {
		String userId = (String) map.get("userId");
		String companyId = (String) map.get("companyId");
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql = new StringBuffer("");

		sql.append("select p.NUMBER_NO ROW_NUMBER\n");
		sql.append("  from TMP_VS_PRICE_DTL p\n");
		sql.append(" where 1 = 1\n");
		sql.append("   and p.USER_ID =" + userId + "\n");
		sql.append("   and not exists (select 1\n");
		sql.append("       from TM_VHCL_MATERIAL_GROUP g\n");
		sql.append("      where g.GROUP_CODE = p.GROUP_CODE\n");
		sql.append("        and g.STATUS = " + Constant.STATUS_ENABLE + "\n");// 有效
		sql.append("        and g.COMPANY_ID = " + companyId + ")\n");
		sql.append("   order by TO_NUMBER(p.NUMBER_NO) asc");

		return dao.pageQuery(sql.toString(), params, getFunName());
	}

	/**
	 * 查询临时表中是否有相同数据
	 * 
	 * @param map
	 * @return
	 */
	public List<Map<String, Object>> talbeCheckDump(Map<String, Object> map) {
		String userId = (String) map.get("userId");
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql = new StringBuffer("");

		sql
				.append("select p1.NUMBER_NO ROW_NUMBER1, p2.NUMBER_NO ROW_NUMBER2\n");
		sql.append("  from TMP_VS_PRICE_DTL p1, TMP_VS_PRICE_DTL p2\n");
		sql.append(" where \n");
		sql.append("   p1.PRICE_CODE = p2.PRICE_CODE\n");
		sql.append("   and p1.GROUP_CODE = p2.GROUP_CODE\n");
		sql.append("   and p1.NUMBER_NO <> p2.NUMBER_NO\n");
		sql.append("   and p1.USER_ID = p2.USER_ID\n");
		sql.append("   and p1.USER_ID= ? \n");
		params.add(userId);
		sql.append("   order by TO_NUMBER(p1.NUMBER_NO)\n");

		List<Map<String, Object>> list = dao.pageQuery(sql.toString(), params,
				getFunName());

		return list;
	}
	
	/**
	 * 根据物料组id进行递归查询
	 * 
	 * @param map
	 * @return
	 */
	public List<Map<String,Object>> getAllMaterialGroup(String groupId){
		StringBuffer sql = new StringBuffer();
		List<Object> params = new LinkedList<Object>();
		sql.append("SELECT G.GROUP_ID\n");
		sql.append("  FROM TM_VHCL_MATERIAL_GROUP G\n");  
		sql.append(" START WITH G.GROUP_ID = "+groupId+"\n");  
		sql.append("CONNECT BY PRIOR G.GROUP_ID = G.PARENT_GROUP_ID\n");
		
		return dao.pageQuery(sql.toString(), params, getFunName());
	}
}
